package dao;

/**
 *
 * @author yakimovich
 */
import bean.Role;
import static dao.AbstractJdbcDao.logger;
import interfaces.RoleDao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author Yana
 */
public class JdbcRoleDao extends AbstractJdbcDao
        implements RoleDao {

    /**
     * Create JdbcRoleDao by default.
     */
    public JdbcRoleDao() {
    }

    /**
     * Create JdbcRoleDao with the specified parameters.
     *
     * @param uri
     * @param username
     * @param password
     * @param driverName
     */
    public JdbcRoleDao(final String uri, final String username,
            final String password, final String driverName) {
        super.uri = uri;
        super.username = username;
        super.password = password;
        super.driverName = driverName;
    }

    /**
     * Create in the table roles a row with the specified role.
     *
     * @param role
     */
    @Override
    public void create(Role role) {
        if (role == null) {
            throw new NullPointerException();
        }

        Connection conn = createConnection();

        PreparedStatement ps = null;
        String name = role.getName();
        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "INSERT INTO ROLES (Name) VALUES (?)");
            ps.setString(1, name);

            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Update in the table roles a row with the specified role.
     *
     * @param role
     */
    @Override
    public void update(Role role) {

        if (role == null) {
            throw new NullPointerException();
        }
        if (!isRoleById(role.getId())) {
            throw new IllegalArgumentException();
        }
        Connection conn = createConnection();

        PreparedStatement ps = null;
        long id = role.getId();
        String name = role.getName();
        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "UPDATE Roles SET Name = ? WHERE Role_id = ?");

            ps.setString(1, name);
            ps.setLong(2, id);

            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Remove in the table roles a row with the specified role.
     *
     * @param role
     */
    @Override
    public void remove(Role role) {
        if (role == null) {
            throw new NullPointerException();
        }
        if (!isRoleById(role.getId())) {
            throw new IllegalArgumentException();
        }
        if (isUserByRole(role)) {
            throw new IllegalArgumentException();
        }
        Connection conn = createConnection();

        PreparedStatement ps = null;
        long id = role.getId();

        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "DELETE FROM Roles WHERE Role_id = ?");
            ps.setLong(1, id);
            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Find in the table roles a role with the specified name.
     *
     * @param name the specified name of a role
     * @return role with the specified name
     */
    @Override
    public Role findByName(String name) {
        if (name == null) {
            throw new NullPointerException();
        }
        if (!isRoleByName(name)) {
            throw new IllegalArgumentException();
        }
        Connection conn = createConnection();

        Role role = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "SELECT role_id, name FROM Roles WHERE name = ?");
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()) {
                long id = rs.getLong("ROLE_ID");
                role = new Role();
                role.setId(id);
                role.setName(name);
            }
            conn.commit();
            return role;
        } catch (SQLException e) {
            logger.error(this, e);
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    private boolean isRoleById(Long role_id) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Roles "
                    + "WHERE Role_id = ?");
            ps.setLong(1, role_id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }

    private boolean isRoleByName(String name) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Roles "
                    + "WHERE name = ?");
            ps.setString(1, name);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }

    private boolean isUserByRole(Role role) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        long role_id = role.getId();

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "WHERE Role_id = ?");
            ps.setLong(1, role_id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }
}
